package edu.pet.DAO;

import edu.pet.entity.*;

import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

public class PetDAO {
	private Connection conn;
	private String sql;
	private ResultSet rs;
	private Statement st;
	private PreparedStatement pst;
	
	public PetDAO(){
		DBConn dBConn = new DBConn();
		conn = dBConn.CreateConn();
	}
	
	//格式化日期
	private static String transDate(String date){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String newDate = "";
		Date d;
		try {
			d = new SimpleDateFormat("yyyy-MM-dd").parse(date);
			newDate = sdf.format(d);
		} catch (ParseException e) {
			System.out.println("err at transDate");
		}
		return newDate;
	}
	
	//登陆验证
	public boolean checkUser(User user){
		boolean flag = false;
		sql = "select * from employee where name = ? and password = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getUsername());
			pst.setString(2, user.getPassword());
			rs = pst.executeQuery();
			if(rs.next())
				flag = true;
		} catch (SQLException e) {
			System.out.println("checkUser()");
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	//查询兽医
	public ArrayList<Vet> searchDoctor(String name,String speciality){
		System.out.println("vetname:"+name+"  sepciality"+speciality);
		ArrayList<Vet> vets = new ArrayList<Vet>();
		if(name!="")
		{
			if(speciality != ""){//姓名&&专业
				System.out.println("姓名&&专业");
				sql = "select v.name as vetname,s.name as specname from vets v " +
					"join vet_specialties v_s on v.id = v_s.vet_id " +
					"join specialties s on v_s.specialty_id = s.id "+
					"where v.name = ? and s.name = ?";
				try {
					pst = conn.prepareStatement(sql);
					pst.setString(1, name);
					pst.setString(2, speciality);
					
				} catch (SQLException e) {
					System.out.println("searchDoctor err 1");
				}
			}else{//姓名
				System.out.println("姓名");
				sql = "select v.name as vetname,s.name as specname from vets v " +
				"join vet_specialties v_s on v.id = v_s.vet_id " +
				"join specialties s on v_s.specialty_id = s.id"+
				" where v.name = ? or s.name = ?";
				try {
					pst = conn.prepareStatement(sql);
					pst.setString(1, name);
					pst.setString(2, speciality);
				} catch (SQLException e) {
					System.out.println("searchDoctor err 3");
				}
			}
		}else{
			if(speciality !=""){//专业
				System.out.println("专业");
				sql = "select v.name as vetname,s.name as specname from vets v " +
				"join vet_specialties v_s on v.id = v_s.vet_id " +
				"join specialties s on v_s.specialty_id = s.id "+
				"where s.name = ?";
				try {
					pst = conn.prepareStatement(sql);
					pst.setString(1,speciality);
				} catch (SQLException e) {
					System.out.println("searchDoctor err 2");
				}
			}else{//全部查询
				System.out.println("全部查询");
				sql = "select v.name as vetname,s.name as specname from vets v " +
				"join vet_specialties v_s on v.id = v_s.vet_id " +
				"join specialties s on v_s.specialty_id = s.id ";
				try {
					pst = conn.prepareStatement(sql);
				} catch (SQLException e) {
					System.out.println("searchDoctor err 4");
				}
			}
		}
		try {
			rs = pst.executeQuery();
			System.out.println(sql);
			while(rs.next()){
				Vet one = new Vet();
				one.setName(rs.getString(1));
				one.setSpecialties(rs.getString(2));
				vets.add(one);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vets;
	}
	
	//查询宠物和主人
	public ArrayList<Pets_Owners> searchPet_Owner(String ownername,String petname){
		ArrayList<Pets_Owners> pets_owners = new ArrayList<Pets_Owners>();
		if(ownername!=""){
			if(petname!=""){//按宠物名 主人名查找
				System.out.println("按宠物名 主人名查找");
				sql="select pets.id as pet_id,pets.name as pet_name,owner.id as owner_id,owner.name as owner_name from " +
						"pets join owner on pets.owner_id = owner.id " +
						"where pets.name like '%" + petname +"%' or owner.name like '%" + ownername +"%'";
			}else{//按主人名查找
				System.out.println("按主人名查找");
				sql="select pets.id as pet_id,pets.name as pet_name,owner.id as owner_id,owner.name as owner_name from " +
				"pets join owner on pets.owner_id = owner.id " +
				"where owner.name like '%" + ownername +"%'";
			}
		}else{
			if(petname!=""){//按宠物名查找
				System.out.println("按宠物名查找");
				sql="select pets.id as pet_id,pets.name as pet_name,owner.id as owner_id,owner.name as owner_name from " +
				"pets join owner on pets.owner_id = owner.id " +
				"where pets.name like '%" + petname +"%'";
			}else{//全部查找
				System.out.println("全部查找");
				sql="select pets.id as pet_id,pets.name as pet_name,owner.id as owner_id,owner.name as owner_name from " +
				"pets join owner on pets.owner_id = owner.id ";
			}
		}
		try {
			System.out.println(sql);
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Pets_Owners one = new Pets_Owners();
				one.setOwner_id(rs.getInt("owner_id"));
				one.setOwner_name(rs.getString("owner_name"));
				one.setPet_id(rs.getInt("pet_id"));
				one.setPet_name(rs.getString("pet_name"));
				pets_owners.add(one);
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at searchPet_Owners");
		}
		return pets_owners;
	}
	
	//按宠物id查找单个宠物
	public Pet searchPet(int petid){
		Pet pet = new Pet();
		sql = "select pets.name as pet_name,types.name as type_name,pets.birth_date as pet_birthday," +
		"pets.owner_id as owner_id,owner.name as ownername from " +
		"pets join types on pets.type_id = types.id " +
		"join owner on pets.owner_id = owner.id " +
		"where pets.id = "+ petid;
		System.out.println(sql);
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				pet.setPet_id(petid);
				pet.setName(rs.getString(1));
				pet.setType(rs.getString(2));
				pet.setBirthday(transDate(rs.getString(3)));//调用transDate方法格式化时间
				pet.setOwner_id(rs.getInt(4));
				pet.setOwner_name(rs.getString(5));
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at searchPet");
		}
		return pet;
		
	}
	
	//按照主人id查找主人信息
	public Owner searchOwner(int ownerid){
		Owner owner = new Owner();
		owner.setOwner_id(ownerid);
		sql = "SELECT NAME,address,city,telephone FROM OWNER WHERE id = "+ ownerid;
		System.out.println(sql);
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				owner.setName(rs.getString(1));
				owner.setAddress(rs.getString(2));
				owner.setCity(rs.getString(3));
				owner.setTelephone(rs.getString(4));
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at searchOwner");
		}
		return owner;
	}
	
	/**
	 * 跟新主人信息
	 * @param owner
	 * @return
	 */
	public boolean updateOwner(Owner owner){
		boolean flag = false;
		sql = "update owner set name=?,address=?,city=?,telephone=? where id=?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, owner.getName());
			pst.setString(2, owner.getAddress());
			pst.setString(3, owner.getCity());
			pst.setString(4, owner.getTelephone());
			pst.setInt(5, owner.getOwner_id());
			if(pst.executeUpdate() == 1)
				flag = true;
		} catch (SQLException e) {
			System.out.println("err at updateOwner");
		}
		return flag;
	}

	/**
	 * 添加主人
	 * @param owner
	 * @return
	 */
	public boolean addOwner(Owner owner){
		boolean flag = false;
		sql = "insert into owner(name,address,city,telephone) values(?,?,?,?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, owner.getName());
			pst.setString(2, owner.getAddress());
			pst.setString(3, owner.getCity());
			pst.setString(4, owner.getTelephone());
			if(pst.executeUpdate() == 1){
				flag = true;
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at addOwner");
		}
		return flag;
	}

	/**
	 * 获取宠物类型
	 * @param owner
	 * @return
	 */
	public ArrayList<Type> getType(){
		ArrayList<Type> types = new ArrayList<Type>();
		sql = "select * from types";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Type one = new Type();
				one.setType_id(rs.getInt(1));
				one.setType(rs.getString(2));
				types.add(one);
			}
		} catch (SQLException e) {
			System.out.println("err at getTypes");
		}
		return types;
	}

	/**
	 * 添加宠物
	 * @param owner
	 * @return
	 */
	public boolean addPet(Pet pet){
		boolean flag = false;
		sql = "INSERT INTO pets(NAME,birth_date,type_id,owner_id) VALUES(?,?,?,?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, pet.getName());
			pst.setString(2, pet.getBirthday());
			pst.setInt(3, pet.getType_id());
			pst.setInt(4, pet.getOwner_id());
			if(pst.executeUpdate() == 1)
				flag = true;
		} catch (SQLException e) {
			System.out.println("err at addPet");
		}
		return flag;
	}
	/**
	 * 修改宠物信息
	 * @param owner
	 * @return
	 */
	public boolean updatePet(Pet pet){
		boolean flag = false;
		sql = "UPDATE pets SET NAME = ?,birth_date = ?,type_id = ?,owner_id = ? WHERE id = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, pet.getName());
			pst.setString(2,pet.getBirthday());
			pst.setInt(3, pet.getType_id());
			pst.setInt(4, pet.getOwner_id());
			pst.setInt(5, pet.getPet_id());
			if(pst.executeUpdate()==1)
				flag = true;
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at updatePet");
		}
		return flag;
	}

	/**
	 * 获取主人列表
	 * @param owner
	 * @return
	 */
	public ArrayList<Owner> getOwner(){
		ArrayList<Owner> owners = new ArrayList<Owner>();
		sql = "select id,name from owner";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Owner one = new Owner ();
				one.setOwner_id(rs.getInt(1));
				one.setName(rs.getString(2));
				owners.add(one);
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at getOwner");
		}
		return owners;
	}

	/**
	 * 根据宠物id查找病例
	 * @param owner
	 * @return
	 */
	public ArrayList<History> queryHistory(int petid){
		ArrayList<History> historyList = new ArrayList<History>();
		sql = "SELECT visit_date,description FROM visits WHERE pet_id = " + petid;
		System.out.println(sql);
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				History one = new History();
				one.setDate(transDate(rs.getString(1)));
				one.setDescirption(rs.getString(2));
				System.out.println(one.getDate() + one.getDescirption());
				historyList.add(one);
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at queryHistory");
		}
		
		return historyList;
	}

	/**
	 * 添加病例
	 * @param owner
	 * @return
	 */
	public boolean addHistory(History history,int petid){
		boolean flag = false;
		sql = "INSERT INTO visits (pet_id,visit_date,description) VALUES (?,?,?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, petid);
			pst.setString(2, history.getDate());
			pst.setString(3, history.getDescirption());
			if(pst.executeUpdate() == 1){
				flag = true;
			}
			conn.close();
		} catch (SQLException e) {
			System.out.println("err at addHistory");
		}
		return flag;
	}
	
	
	public List<Vet> listVets(){
		List<Vet> vetList = new ArrayList<Vet>();
		sql = "SELECT v.id,v.name,v.address,v.telephone,s.name FROM vets v "+
				" JOIN vet_specialties vs ON v.id = vs.vet_id " +
				" JOIN specialties s ON vs.specialty_id = s.id";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Vet v = new Vet();
				v.setId(rs.getInt(1));
				v.setName(rs.getString(2));
				v.setAddress(rs.getString(3));
				v.setTelephone(rs.getString(4));
				v.setSpecialties(rs.getString(5));
				vetList.add(v);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vetList;
	}
	
	//通过id查找兽医
	public Vet findVetById(int id){
		Vet vet = new Vet();
		sql = "select * from vets where id = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			while(rs.next()){
				vet.setId(id);
				vet.setName(rs.getString("name"));
				vet.setAddress(rs.getString("address"));
				vet.setTelephone(rs.getString("telephone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return vet;
	}
	//查找所有兽医特长
	public List<Specialties> findAllSpecialies(){
		List<Specialties> specList = new ArrayList<Specialties>();
		sql = "select * from specialties";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Specialties spec = new Specialties();
				spec.setId(rs.getInt("id"));
				spec.setName(rs.getString("name"));
				specList.add(spec);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return specList;
	}
	
	public void addPetType(Type type){
		sql = "insert into types(name) values(?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, type.getType());
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public void editPetType(Type type){
		sql = "update types set name = ? where id = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, type.getType());
			pst.setInt(2, type.getType_id());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean delType(int id) {
		boolean flag = false;
		sql = "select * from pets where type_id = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				flag = false;
			}else{
				sql = "delete from types where id = ?";
				pst = conn.prepareStatement(sql);
				pst.setInt(1, id);
				pst.executeUpdate();
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	/**
	 * 添加专长
	 * @param spec
	 */
	public void addSpec(Specialties spec) {
		sql = "insert into specialties(name) values (?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, spec.getName());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//编辑兽医
	public void editVet(Vet vet) {
		sql = "update vets set name = ?,address=?,telephone=? where id = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, vet.getName());
			pst.setString(2, vet.getAddress());
			pst.setString(3, vet.getTelephone());
			pst.setInt(4, vet.getId());
			pst.executeUpdate();
			sql = "update vet_specialties set specialty_id = ? where vet_id=?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, vet.getSpecId());
			pst.setInt(2, vet.getId());
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//添加兽医
	
	public void addVet(Vet vet){
		try {
			sql = "insert into vets(name,address,telephone) values(?,?,?);";
			pst = conn.prepareStatement(sql);
			pst.setString(1, vet.getName());
			pst.setString(2, vet.getAddress());
			pst.setString(3, vet.getTelephone());
			if(pst.executeUpdate()==1){
				sql = "select id from vets where name = ? and address = ? and telephone = ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, vet.getName());
				pst.setString(2, vet.getAddress());
				pst.setString(3, vet.getTelephone());
				rs = pst.executeQuery();
				if(rs.next()){
					int vetId = 0;
					vetId = rs.getInt(1);
					sql = "insert into vet_specialties(vet_id,specialty_id) values(?,?);";
					pst = conn.prepareStatement(sql);
					pst.setInt(1, vetId);
					pst.setInt(2, vet.getSpecId());
					pst.executeUpdate();
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public void delVet(int id) {
		try {
			sql = "delete from vet_specialties where vet_id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			pst.executeUpdate();
			sql = "delete from vets where id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void editSpec(Specialties spec) {
		try {
			sql = "update specialties set name = ? where id = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, spec.getName());
			pst.setInt(2, spec.getId());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public boolean delSpec(int id) {
		boolean flag = false;
		try {
			sql = "select * from vet_specialties where specialty_id =?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				flag = false;
			}else{
				sql = "delete from specialties where id = ?";
				pst = conn.prepareStatement(sql);
				pst.setInt(1, id);
				pst.executeUpdate();
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public void addUser(User user) {
		sql = "insert into employee(name,password) values(?,?);";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getUsername());
			pst.setString(2, user.getPassword());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public void editUser(User user) {
		sql = "update employee set password = ? where name = ?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getPassword());
			pst.setString(2, user.getUsername());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}
	